--IF(OBJECT_ID(N'SiteManager..Temp_Payments_PPD_Failed_Trxns',N'U') IS NOT NULL)
--BEGIN
--	DROP TABLE SiteManager..Temp_Payments_PPD_Failed_Trxns
--END
--
--
----delete all temp tables
--IF(object_id(N'TempDB..#Temp_ppd',N'U') IS NOT NULL)
--BEGIN
--	DROP TABLE #Temp_ppd
--END



SET ANSI_WARNINGS OFF
SET NOCOUNT ON
DECLARE @fixCode varchar(max)
	,@db varchar(20),@ent varchar(20)
	,@sitestr varchar(10)
	,@Dcounter int

-- THIS IS USED FOR PULLING PREAUTH FOR SEPARATE PMCs 
declare @PMCId varchar(10)
set @PMCId = substring (db_name(), 2,7)



--TRUNCATE TABLE SiteManager..Temp_Payments_PPD_Failed_Trxns

SELECT DISTINCT p.siteID AS x,p.entid
INTO #SiteList
FROM	sitemaster..posprofile P
join servermanager..CONSOLIDATED_ACTIVECENTERS CA on P.entid = CA.entid and CA.sitestatus = 'ACTIVE' and ca.oneID = 37
join master..sysdatabases sd on LTRIM(STR(p.entID)) = SUBSTRING(sd.name, 2, 7)
and SUBSTRING(sd.name, 1, 1) = 'P'
WHERE p.posPMCBit=1
--and p.entid in (1045377,1048487,1050016,1181172,2441510)
order by p.entid

--select *
--from #SiteList

CREATE TABLE #TblParams (StartDate DateTime,SiteID  varchar(8),PMCID varchar(8))

CREATE TABLE #TblParams2 (StartDate DateTime,SiteID  varchar(8),PMCID varchar(8), Info varchar(100))


WHILE EXISTS(SELECT x FROM #SiteList)
BEGIN
	SELECT top 1 @db = 'S' + CAST(x AS varchar(10)),@ent=ltrim(rtrim(CAST(entid AS varchar(10))))
	from #SiteList

	SELECT @sitestr = SUBSTRING(@db,2,8)


	SELECT @fixCode = 'USE ' + @db +'
if exists(select top 1 * from sys.tables where name=''TransActivity'')
begin

DECLARE @RowCount Integer
DECLARE @StartDate DateTime 
DECLARE @SiteID  varchar(8)
DECLARE @PMCID  varchar(8)


INSERT INTO #TblParams
  --SELECT Loginname , MemberOf FROM #serverLoginDetails
	select bsStartDate,SiteID,'+@ent+' 
	from P'+@ent+'.dbo.SchedulerBatchSite 
	where batID in (select batid from P'+@ent+'.dbo.SchedulerBatch where batShortName in (''PreauthorizedScheduler'',''PreauthorizedSchedulerBACKUP'')) 
	and SiteID = '+SUBSTRING(@db, 2, 8)+'
	order by siteID,bsStartDate

If (select count(*) from #TblParams where SiteID = '+SUBSTRING(@db, 2, 8)+')=0
Begin
INSERT INTO #TblParams2
	select null,'+SUBSTRING(@db, 2, 8)+','+@ent+',''NO RECORD''
End



if(select count(*)
from #TblParams
where SiteID = '+SUBSTRING(@db, 2, 8)+'
and datediff(minute,StartDate,getdate()) > 60)>0
begin
	INSERT INTO #TblParams2
	select StartDate,'+SUBSTRING(@db, 2, 8)+','+@ent+',''JOB NOT FINISHED''
	from #TblParams
	where SiteID = '+SUBSTRING(@db, 2, 8)+'
	and datediff(minute,StartDate,getdate()) > 60
end



end
'
--Dynamic SQL Ends Here			

	--select * from master..sysdatabases where name='P1240286'			
	IF EXISTS (SELECT NAME FROM master..sysDatabases WHERE (NAME = @db) and Status not in (56))
	AND EXISTS (SELECT NAME FROM master..sysDatabases WHERE (name = 'P'+@ent) and Status not in (56))
	BEGIN
	Print(@fixCode)
	EXECUTE(@fixCode)
	END

	DELETE  #SiteList WHERE x = SUBSTRING(@db, 2, 8)
END

DROP TABLE #SiteList



DROP TABLE #TblParams

select * from #TblParams2

DROP TABLE #TblParams2

